import sqlite3
import arrow
import json
import arrow
import pandas as pd
import matplotlib
matplotlib.use('agg')
from matplotlib.ticker import MultipleLocator
from matplotlib.dates import DateFormatter
import matplotlib.pyplot as plt

# con = sqlite3.connect('./data/time_series.db')
# cur = con.execute("select * from tsdb_hlct;")

# data = []
# for each in cur:
#     data.append({'time':arrow.get(each[0]).to('local').format('YYYY-MM-DD HH:mm:ss'),'rate':each[2]})

def draw(data):

    print(len(data))
    df = pd.read_json(json.dumps(data))
    df.time = pd.to_datetime(df.time)

    font = {'family' : 'serif',
            'color'  : 'darkred',
            'weight' : 'normal',
            'size'   : 16,
            }


    # fig = plt.figure(111, figsize=(8,5))  # figure 定义一张图片, figsize决定图片的大小
    # ax =  fig.add_axes([0.12, 0.12, 0.82, 0.8])  # 在图片上定义一块绘图区域，可以通过 fig.gca() 获得
    # ax.plot(df.time,df.rate)

    fig, ax = plt.subplots()

    # ax.xaxis.set_major_formatter(dates.DateFormatter('%H:%M:%S'))#设置时间标签显示格式
    # plt.xticks(pd.date_range(df.time[0],df.time[-1],freq='1min'))

    plt.plot(df.time,df.rate)
    # plt.xlim(arrow.now().replace(hours=+4).datetime,)

    xaxis = ax.xaxis  # 获取绘图区域的x轴
    ax.set_ylabel('%/year',fontdict=font)
    # xaxis.set_major_locator(MultipleLocator(1/24/60*25))  # x轴的坐标间隔设置为1
    xaxis.set_major_formatter(DateFormatter('%dd %H:%M'))  # 格式化日期
    fig.autofmt_xdate()  # 自动格式化日期，防止其溢出绘图区域
    plt.title("Interest rates in the last 4 hours",fontdict=font)
    # plt.grid()

    fig.savefig('rate.png')
    return 'rate.png'

if __name__ == '__main__':
    con = sqlite3.connect('./data/time_series.db')
    cur = con.execute("select * from tsdb_hlct ORDER BY updatetime DESC Limit 500;")

    data = []
    for each in cur:
        data.append({'time':arrow.get(each[0]).to('local').format('YYYY-MM-DD HH:mm:ss'),'rate':each[2]})
    draw(data)
